package com.elitel.frame.base.controller;

import com.alibaba.druid.util.StringUtils;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.elitel.common.utils.XCommonUtils;
import com.elitel.frame.base.component.RedisCacheComponent;
import com.elitel.frame.base.component.RedisCacheDataBase;
import com.elitel.frame.base.service.*;
import com.elitel.frame.business.service.DataBaseMetadataService;
import com.elitel.frame.main.entity.*;
import com.elitel.frame.main.entity.ext.BaseFieldaddonsExt;
import com.elitel.frame.main.entity.ext.BaseFieldconfigExt;
import com.elitel.frame.main.entity.vo.PageRequestVo;
import com.elitel.frame.main.entity.vo.PageResponseVo;
import com.elitel.frame.main.entity.vo.ZTreeDataModelVo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @Description 元数据视图配置服务
 * @author guoyanfei
 */
@Controller
@RequestMapping("/system/dataView")
public class DataViewController {
	private static final Logger logger = LoggerFactory.getLogger(DataConfigController.class);
	@Autowired
	private CfgTableService cfgTableService;
	@Autowired
	private RedisCacheComponent redisCacheComponent;
	@Autowired
	private RedisCacheDataBase redisCacheDataBase;
	@Autowired
	private CfgFieldService cfgFieldService;
	@Autowired
	private BaseFieldconfigService baseFieldconfigService;
	@Autowired
	private BaseFieldaddonsService baseFieldaddonsService;
	@Autowired
	private CfgDbService cfgDbService;
	@Autowired
	private DataBaseMetadataService dataBaseMetadataService;
	@Autowired
	private BaseFieldenumService baseFieldenumService;
	
	/**
	 * @Title: getTreeList 
	 * @Description: TODO(获取树形元数据列表集合) 
	 * @author guoyanfei
	 * @return List<ZTreeDataModelVo>(ZTree数据格式集合)
	 * @throws
	 */
	@RequestMapping("/getTreeList")
	@ResponseBody
	public List<ZTreeDataModelVo> getTreeList(
			HttpServletRequest request, HttpServletResponse response)
	{
		List<ZTreeDataModelVo> zTreeDataModelVos = new ArrayList<ZTreeDataModelVo>();
		try {
				zTreeDataModelVos = redisCacheDataBase.getTreeViewListRedisCache();
		} catch (Exception e) {
			logger.error(e.getMessage(),e);
		}
		return zTreeDataModelVos;
	}
	
	/**
	 * @Description 通过数据库标识查询元数据表集合信息
	 * @return CfgTable集合
	 * @author guoyanfei
	 */
	@RequestMapping("/getTableList")
	@ResponseBody
	public List<CfgTable> getTableList(HttpServletRequest request){
		List<CfgTable> cfgTables = new ArrayList<>();
		try {
			String dbkey = request.getParameter("dbkey");
			cfgTables = cfgTableService.getListByConnAndtableType(dbkey, 1);
		} catch (Exception e) {
			logger.error(e.getMessage(),e);
		}
		return cfgTables;
	}
	
	/**
	 * @Title: deleteTableBydtConfigId 
	 * @Description: TODO(通过表标识删除配置表信息)
	 * @author guoyanfei
	 * @return 删除结果
	 * @throws
	 */
	@RequestMapping("/delTableInfo")
	@ResponseBody
	public int deleteTableBydtConfigId(HttpServletRequest request)
	{
		int result = 0;
		try {
			String dtConfigId = request.getParameter("dtConfigId");
			result = cfgTableService.deleteTableInfo(dtConfigId);
			//刷新Tree缓存
			redisCacheDataBase.refreshTreeViewRedisCache();
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage(),e);
		}
		
		return result;
	}
	
	/**
	 * @Title: getdtconfigIs 
	 * @Description: TODO(查询表标识在数据库中是否存在)
	 * @author guoyanfei
	 * @return 查询结果
	 * @throws
	 */
	@RequestMapping("/getdtConfigId")
	@ResponseBody
	public String getdtconfigIs(
			HttpServletRequest request,HttpServletResponse response)
	{
		String result = "";
		try {
			String dtConfigId = request.getParameter("dtConfigId");
			result = cfgTableService.getdtConfigIdIs(dtConfigId);
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage(),e);
		}
		
		return result;
	}
	
	/**
	 * @Description 通过表标识查询表信息
	 * @param request
	 * @return CfgTable集合
	 * @author guoyanfei
	 */
	@RequestMapping("/getTabelInfobyConfigid")
	@ResponseBody
	public CfgTable getTabelInfobyConfigid(HttpServletRequest request){
		CfgTable cfgTable = new CfgTable();
		try {
			String dtConfigId = request.getParameter("dtConfigId");
			String tableType = request.getParameter("tableType");
			cfgTable = cfgTableService.getTableBydtConfigIdAndTableType(dtConfigId, Integer.valueOf(tableType) );
		} catch (Exception e) {
			logger.error(e.getMessage(),e);
		}
		return cfgTable;
	}
	
	/**
	 * @Title: adddtTable 
	 * @Description: TODO(新增视图配置信息)
	 * @author guoyanfei
	 * @return 插入结果
	 * @throws
	 */
	@RequestMapping("/add")
	@ResponseBody
	public int adddtTable(@RequestBody CfgTable cfgTable,
			HttpServletRequest request,HttpServletResponse response)
	{
		int result = 0;
		try {
			Date datenow = new Date();
			SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			cfgTable.setGuid(UUID.randomUUID().toString());
			cfgTable.setCreatedate(dateFormat.format(datenow));
			cfgTable.setDtTabletype(2);
			result = cfgTableService.addcfgTable(cfgTable);
			if(result > 0){
				redisCacheDataBase.refreshTreeViewRedisCache();
			}
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage(),e);
		}
		
		return result;
	}
	
	/**
	 * @Title: updatedtTable 
	 * @Description: TODO(修改视图配置信息)
	 * @author guoyanfei
	 * @return 修改结果
	 * @throws
	 */
	@RequestMapping("/update")
	@ResponseBody
	public int updatedtTable(@RequestBody CfgTable cfgTable,
			HttpServletRequest request,HttpServletResponse response)
	{
		int result = 0;
		try {
			result = cfgTableService.updatecfgTable(cfgTable);
			redisCacheComponent.freshCfgTableRedisCache(cfgTable.getDtConn());
			redisCacheDataBase.refreshTreelistRedisCache();
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage(),e);
		}
		
		return result;
	}
	
	/**
	 * @Description 通过表标识查询主表字段信息
	 * @param request
	 * @return
	 */
	@RequestMapping("getFieldBydtConfigId")
	@ResponseBody
	public List<CfgField> getFieldBydtConfigId(HttpServletRequest request){
		List<CfgField> cfgFields = new ArrayList<>();
		try {
			String dtConfigId = request.getParameter("dtConfigId");
			CfgTable cfgTable = cfgTableService.getInfoByDtconfigId(dtConfigId);
			if(cfgTable != null){
				cfgFields = cfgFieldService.getAllField(cfgTable.getDtName());
			}
		} catch (Exception e) {
			logger.error(e.getMessage(),e);
		}
		return cfgFields;
	}
	
	/**
	 * @Description 通过表标识查询外键表字段信息
	 * @param request
	 * @return
	 */
	@RequestMapping("getFieldByConfigId")
	@ResponseBody
	public List<CfgField> getFieldByConfigId(HttpServletRequest request){
		List<CfgField> cfgFields = new ArrayList<>();
		try {
			String dtConfigId = request.getParameter("dtConfigId");
			cfgFields = cfgFieldService.getAllField(dtConfigId);
		} catch (Exception e) {
			logger.error(e.getMessage(),e);
		}
		return cfgFields;
	}
	
	/**
	 * @Title: editFieldConfigInfo 
	 * @Description: TODO(维护元数据配置-关联表配置信息)
	 * @author guoyanfei
	 * @return Integer(插入结果)
	 * @throws
	 */
	@RequestMapping("/editFieldConfigInfo/{dtConfigId}")
	@ResponseBody
	public Integer editFieldConfigInfo(@PathVariable String dtConfigId, @RequestBody List<BaseFieldconfig> baseFieldconfigList){
		Integer result=0;
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try {
			baseFieldconfigService.batchDelete(dtConfigId);
			for ( BaseFieldconfig baseFieldconfig: baseFieldconfigList) {
				if (StringUtils.isEmpty(baseFieldconfig.getGuid())) {
					//设置别名
					baseFieldconfig.setAltername(setAlterName(baseFieldconfig.getDtConfigId()));
					baseFieldconfig.setGuid(UUID.randomUUID().toString());
					Date datenow = new Date();
					baseFieldconfig.setCreatetime(df.format(datenow));
					result =baseFieldconfigService.insertInfo(baseFieldconfig);
				}else{
					result = baseFieldconfigService.updateInfo(baseFieldconfig);
				}
				//刷新缓存
				if(baseFieldconfig.getDtConfigId().equals("") == false){
					redisCacheDataBase.refreshFieldConfigRedisCache(baseFieldconfig.getDtConfigId());
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage(),e);
		}
		
		return result;
	}
	
	/**
	 * @Description 设置关系表别名
	 * @param dtConfigId
	 * @return 别名
	 * @author guoyanfei
	 */
	private String setAlterName(String dtConfigId){
		String result = "A";
		String alterNames = "A-B-C-D-E-F-G-H-I-J-K-L-M-N-O-P-Q-R-S-T-U-V-W-X-Y-Z";
		String name = baseFieldconfigService.getAlterNameBydtConfigId(dtConfigId);
		if(name !=""){
			int i=0;
			String[] names = alterNames.split("-");
			for (String str : names) {
				if(str.equals(name)){
					break;
				}
				i++;
			}
			if(i<names.length){
				result = names[i+1];
			}
		}
		return result;
	}
	
	/**
	 * @Title: getFieldConfigBypage 
	 * @Description: TODO(通过表标识分页查询元数据配置-关联表配置信息)
	 * @author guoyanfei
	 * @return PageResponseVo<BaseFieldconfig>(分页数据集合)
	 * @throws
	 */
	@RequestMapping("/getFieldConfigBypage")
	@ResponseBody
	public PageResponseVo<BaseFieldconfigExt> getFieldConfigBypage(
			HttpServletRequest request){
		PageResponseVo<BaseFieldconfigExt> pageResponseVo = new PageResponseVo<BaseFieldconfigExt>();
		try {
			int pageNo = Integer.valueOf(request.getParameter("pageNo")).intValue();
			int pageSize = Integer.valueOf(request.getParameter("pageSize")).intValue();
			String dtConfigId = request.getParameter("dtConfigId");
			PageRequestVo<BaseFieldconfigExt> pr = new PageRequestVo<BaseFieldconfigExt>();
			pr.setPageNo(pageNo);
			pr.setPageSize(pageSize);
			pr.setSearchContent(dtConfigId);
			pageResponseVo = baseFieldconfigService.getListByPage(pr); 
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage(),e);
		}
		
		return pageResponseVo;
	}
	
	/**
	 * @Title: batchDelInfo 
	 * @Description: TODO(批量删除元数据配置-关联表配置信息)
	 * @author guoyanfei
	 * @return 
	 * @throws
	 */
	@RequestMapping("/batchDelInfo")
	@ResponseBody
	public void batchDelInfo(HttpServletRequest request){
		try {
			String guids = request.getParameter("guids");
			baseFieldconfigService.batchDelInfo(guids);
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage(),e);
		}
		
	}
	
	/**
	 *  @Title: getFieldAddonsList 
     * @Desciption:TODO( 通过表标识查询该表所有外键表及其字段信息)
     * @return 表和字段集合
     * @author guoyanfei
     */
	@RequestMapping("/getFieldAddonsList")
	@ResponseBody
	public List<ZTreeDataModelVo> getFieldAddonsList(HttpServletRequest request){
		List<ZTreeDataModelVo> zTreeDataModelVos = null;
		try {
			String dtconfigid = request.getParameter("dtconfigid");
			if(dtconfigid != null && dtconfigid.isEmpty() == false){
				zTreeDataModelVos = cfgFieldService.selectFieldToJoinTable(dtconfigid);
			}
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage(),e);
		}
		return zTreeDataModelVos;
	}
	
	/**
	 *  @Title: getFieldAddonsBydtconfig 
     * @Desciption:TODO( 通过表标识查询基本表附加信息)
     * @return 基本表附加信息
     * @author guoyanfei
     */
	@RequestMapping("/getFieldAddonsBydtconfig")
	@ResponseBody
	public List<BaseFieldaddons> getFieldAddonsBydtconfig(HttpServletRequest request){
		List<BaseFieldaddons> baseFieldaddons = new ArrayList<BaseFieldaddons>();
		try {
			String dtconfigid = request.getParameter("dtconfigid");
			baseFieldaddons = baseFieldaddonsService.selectFieldaddonsBydtconfigid(dtconfigid);
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage(),e);
		}
		return baseFieldaddons;
	}
	
	/**
	 *  @Title: insertFieldAddons 
     * @Desciption:TODO(批量新增基本表附加属性信息)
     * @author guoyanfei
     */
	@RequestMapping("/insertFieldAddons")
	@ResponseBody
	public void insertFieldAddons(HttpServletRequest request){
		try {
			String guids = request.getParameter("guids");
			String dtconfigid = request.getParameter("dtconfigid");
			baseFieldaddonsService.insertFieldaddons(guids, dtconfigid);
			//刷新缓存
			redisCacheDataBase.refreshFieldaddonsRedisCache(dtconfigid);
		} catch (Exception e) {
			logger.error(e.getMessage(),e);
		}
	}
	
	/**
	 *  @Title: getFieldAddonsBydtconfig 
     * @Desciption:TODO( 通过表标识查询扩展基本表附加信息(包括字段名称和中文名称))
     * @return 扩展基本表附加信息
     * @author guoyanfei
     */
	@RequestMapping("/getExtFieldAddonsListBydtconfig")
	@ResponseBody
	public List<BaseFieldaddonsExt> getExtFieldAddonsListBydtconfig(HttpServletRequest request){
		List<BaseFieldaddonsExt> baseFieldconfigExts = new ArrayList<BaseFieldaddonsExt>();
		try {
			String dtconfigid = request.getParameter("dtconfigid");
			baseFieldconfigExts = baseFieldaddonsService.selectExtendFieldaddonsList(dtconfigid);
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage(),e);
		}
		return baseFieldconfigExts;
	}

	@RequestMapping("/getShowFieldListForPageViewByServiceId")
	@ResponseBody
	public List<BaseFieldaddonsExt> getShowFieldListForPageViewByServiceId(HttpServletRequest request){
		List<BaseFieldaddonsExt> baseFieldconfigExts = new ArrayList<BaseFieldaddonsExt>();
		try {
			String serviceID = request.getParameter("serviceID");
			baseFieldconfigExts = baseFieldaddonsService.getShowFieldListForPageView(serviceID);
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage(),e);
		}
		return baseFieldconfigExts;
	}

	
	/**
	 *  @Title: updateFieldAddonns 
     * @Desciption:TODO( 修改基本表附加属性信息)
     * @return 修改结果
     * @author guoyanfei
     */
	@RequestMapping("/updateFieldAddonns")
	@ResponseBody
	public Integer updateFieldAddonns(@RequestBody BaseFieldaddons bf){
		Integer result =0;
		try {
			result = baseFieldaddonsService.updateFieldaddons(bf);
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage(),e);
		}
		return result;
	}
	
	/**
	 * @return PageResponseVo<Map < String, Object>>(分页显示bootstrap Table数据)
	 * @throws
	 * @Title: getTableViewData
	 * @Description: TODO(预览分页查询配置好的元数据信息)
	 * @author guoyanfei
	 */
	@RequestMapping("/viewData")
	@ResponseBody
	public JSONObject getTableViewData(HttpServletRequest request)
	{
		int pageNo = Integer.valueOf(request.getParameter("pageNo")).intValue();
		int pageSize = Integer.valueOf(request.getParameter("pageSize")).intValue(); 
		int BeginIndex=(pageNo-1)*pageSize+1;
		int EndIndex=pageNo*pageSize;
		String dtConfigId = request.getParameter("dtConfigId");
		StringBuilder sqltxt = new StringBuilder();
		PageResponseVo<Map<String, Object>> responseVo = new PageResponseVo<Map<String, Object>>();
		CfgDb cfgDb = cfgDbService.getDbInfoBydtconfigID(dtConfigId);
		CfgTable cfgtable = cfgTableService.getDataViewBydtconfigId(dtConfigId);
		List<BaseFieldconfigExt> fieldconfiglist = baseFieldconfigService.getAllFieldConfig(dtConfigId);
		List<BaseFieldaddonsExt> fieldaddonslist =  baseFieldaddonsService.selectExtendFieldaddonsList(dtConfigId);
		if(cfgtable != null)
		{
			String tableName = cfgtable.getDtName();//主表名称
			String fields="";
			StringBuffer tabletxt = new StringBuffer();//多表关联
			StringBuffer fieldtxt = new StringBuffer();//显示列
			StringBuffer wheretxt = new StringBuffer();//默认关联条件
			tabletxt.append(tableName);
			//设置关联表
			if(fieldconfiglist != null && fieldconfiglist.size() > 0){
				Map<String, List<BaseFieldconfigExt>> baseFieldconfigByGroup = fieldconfiglist.stream().collect(Collectors.groupingBy(o -> o.getTableName()));
        baseFieldconfigByGroup
            .keySet()
            .forEach(
                tableNameKey -> {
					List<BaseFieldconfigExt> baseFieldconfigExts = baseFieldconfigByGroup.get(tableNameKey);
					// 关联关系
					switch (baseFieldconfigExts.get(0).getRelationType()) {
						case "1": // 内连接
							tabletxt.append(" inner join ");
							break;
						case "2": // 左连接
							tabletxt.append(" left join ");
							break;
						case "3": // 右连接
							tabletxt.append(" right join ");
							break;
						default:
							break;
					}
					tabletxt.append(tableNameKey + " on ");
					String condition = "";
					for ( BaseFieldconfigExt bfconfigext: baseFieldconfigExts) {
						if(!StringUtils.equals(condition, "")){
							condition += " and ";
						}
						condition += bfconfigext.getTableName() + "." + bfconfigext.getForeignCode()+ " = " + cfgtable.getDtName() + "." + bfconfigext.getFieldid();
					}
					tabletxt.append(condition);
                });
			}
			//设置显示字段
			if(fieldaddonslist != null && fieldaddonslist.size() > 0){
				for (BaseFieldaddonsExt bfieldaddonsExt : fieldaddonslist) {
					//查询字段对应表别名
					String tablealterName = bfieldaddonsExt.getAltername();

					String strField="";
					//设置列枚举
					if(bfieldaddonsExt.getIsenum() != null && bfieldaddonsExt.getIsenum().equals("1")){
						BaseFieldenum baseFieldenum = baseFieldenumService.getBaseFieldenumByguid(bfieldaddonsExt.getFieldid());
						if(baseFieldenum.getEnumtype() == 0){
							//自定义JSON
							strField+="(CASE "+tablealterName+"."+baseFieldenum.getFieldname();
							Map<String,String> enumMap = (Map<String,String>) JSON.parse(baseFieldenum.getEnumvalue().toString());
							for (String key:enumMap.keySet()){
								String value = enumMap.get(key).toString();
								strField+=" WHEN '"+key+"' THEN '"+value+"' ";
							}
							strField+=" END)";
						}else if(baseFieldenum.getEnumtype() == 1){
							//SQL语句
							strField += "(select enum_value from(";
							strField += baseFieldenum.getEnumvalue();
							strField += ") enum where enum.enum_key="+tablealterName+"."+baseFieldenum.getFieldname()+")";
						}
						if(bfieldaddonsExt.getIsmodfname() != null && bfieldaddonsExt.getIsmodfname().toString().equals("1")){//设置别名
							fieldtxt.append(strField+" as "+bfieldaddonsExt.getModifieldname()+",");
						}else{
							fieldtxt.append(strField+bfieldaddonsExt.getFieldname()+",");
						}
					}else{
						strField=tablealterName+"."+bfieldaddonsExt.getFieldname();

						if(bfieldaddonsExt.getIsmodfname() != null && bfieldaddonsExt.getIsmodfname().toString().equals("1")){//设置别名
							fieldtxt.append(strField+" as "+bfieldaddonsExt.getModifieldname()+",");
						}else{
							fieldtxt.append(strField+" as "+ tablealterName + "_" + bfieldaddonsExt.getFieldname() +" ,");
						}
					}
				}
			}
			fields = fieldtxt.toString();
			if(XCommonUtils.isNotEmpty(fields)){
				fields = fields.substring(0,fields.length()-1);
			}else{
				fields = " * ";
			}
			//设置排序字段
			CfgTable table = cfgTableService.getInfoByDtconfigId(dtConfigId);
			String sort = "";
			if(table != null){
				if(table.getDtOrder() != null && table.getDtOrder().equals("") ==false){
					sort =" order by "+table.getDtOrder();
				}
			}

			//拼接查询sql数据库包括(ORACLE,MYSQL,SQLSERVER)
			if(cfgDb.getDbVersion().equals("ORACLE")){
				sqltxt.append("SELECT * ");
				sqltxt.append("FROM (");
				sqltxt.append("SELECT a.*, ROWNUM RN FROM (SELECT "+fields+" FROM ");
				sqltxt.append(tabletxt.toString()+" "+wheretxt.toString()+" "+sort);
				sqltxt.append(")a WHERE ROWNUM <="+EndIndex+") WHERE RN >="+BeginIndex+"");
			}else if(cfgDb.getDbVersion().equals("MYSQL")) {
				sqltxt.append(" SELECT ");
				sqltxt.append(fields+" ");
				sqltxt.append(" FROM "+tabletxt.toString()+" "+wheretxt.toString()+" "+sort);
				sqltxt.append(" limit ");
				sqltxt.append(BeginIndex-1+","+pageSize+"");
			}else if(cfgDb.getDbVersion().equals("SQLSERVER")) {
				 sqltxt.append("select * from( ");
				 sqltxt.append("select ROW_NUMBER()over("+sort+")RN,");
				 sqltxt.append(fields+" from "+tabletxt.toString()+" "+wheretxt.toString()+" "+" )a ");
				 sqltxt.append("WHERE RN Between "+BeginIndex+" and "+EndIndex);
			}
			List<Map<String, Object>> rows = dataBaseMetadataService.getSqlResult(cfgDb, sqltxt.toString());
			
			String sqlcount = "select count(*)NUM from "+tabletxt.toString()+" "+wheretxt.toString()+" ";
			List<Map<String, Object>> total = dataBaseMetadataService.getSqlResult(cfgDb, sqlcount);
	       
			responseVo.setRows(rows);
	        responseVo.setTotal(Integer.valueOf(total.get(0).get("NUM").toString()));
		}
		String jsonString = JSON.toJSONString(responseVo);
		JSONObject jsonObject = JSON.parseObject(jsonString);
		return jsonObject;
	}
	

}
